﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using RealtorsPortal.Data_Access_Layer;
using System.Data;
using RealtorsPortal.Entities;

namespace RealtorsPortal.Business_Logic_Layer
{
    public class CitiesDAL
    {
        DataAccessHelper daAccHelp = new DataAccessHelper();
        public DataTable getCitiesData()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Cities");
            return dt;
        }

        public DataTable getCitiesDataWithRegions()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Cities_With_Region_Name");
            return dt;
        }

        public int InsertCities(City cityObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryCitiesToInsert(cityObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Insert_Cities", pList);
        }

        public int UpdateCities(City cityObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryCitiesToUpdate(cityObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Update_Cities", pList);
        }

        public int DeleteCities(City cityObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryCitiesToDelete(cityObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Delete_Cities", pList);
        }

        public DataTable GetCitiesByCityName(City cityObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@CityName", SqlDbType.NVarChar);
            para.Value = cityObj.CityName;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Search_By_City", paraList);
            return dt;
        }

        public DataTable GetCitiesByCityId(City cityObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@CityId", SqlDbType.NVarChar);
            para.Value = cityObj.CityId;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Get_City_By_Id", paraList);
            return dt;
        }

        public DataTable GetCitiesByRegionId(City cityObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@RegionId", SqlDbType.Int);
            para.Value = cityObj.RegionId;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Get_City_By_RegionId", paraList);
            return dt;
        }

        public DataTable GetAllByCity(City cityObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@CityName", SqlDbType.NVarChar);
            para.Value = cityObj.CityName;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Get_All_By_Cities", paraList);
            return dt;
        }

        private List<SqlParameter> ExecuteNonQueryCitiesToInsert(City cityObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@CityName", SqlDbType.NVarChar);
            para.Value = cityObj.CityName;
            paraList.Add(para);
            para = new SqlParameter("@RegionId", SqlDbType.NVarChar);
            para.Value = cityObj.RegionId;
            paraList.Add(para);

            return paraList;
        }

        private List<SqlParameter> ExecuteNonQueryCitiesToDelete(City cityObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@CityId", SqlDbType.NVarChar);
            para.Value = cityObj.CityId;
            paraList.Add(para);

            return paraList;
        }

        private List<SqlParameter> ExecuteNonQueryCitiesToUpdate(City cityObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@CityId", SqlDbType.NVarChar);
            para.Value = cityObj.CityId;
            paraList.Add(para);
            para = new SqlParameter("@CityName", SqlDbType.NVarChar);
            para.Value = cityObj.CityName;
            paraList.Add(para);
            para = new SqlParameter("@RegionId", SqlDbType.NVarChar);
            para.Value = cityObj.RegionId;
            paraList.Add(para);

            return paraList;
        }

        public DataTable GetCityByDistrictId(string districtId)
        {
            string cmd = "select c.* from Cities as c, Districts as d where c.CityId = d.CityId and d.DistrictId =" + districtId;
            return daAccHelp.GetDataTable(cmd);
        }
    }
}